{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tabula\n",
    "import pandas as pd\n",
    "import re\n",
    "from json import load, dump"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>school</th>\n",
       "      <th>list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avery Ao</td>\n",
       "      <td>Taiyuan</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dongrui Bai</td>\n",
       "      <td>Xian</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tony Bai</td>\n",
       "      <td>Beijing 100062</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Engineering</td>\n",
       "      <td>Dean's Honor List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Yuchen Bai</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Xinya Bi</td>\n",
       "      <td>Jinan City Shandong</td>\n",
       "      <td>China</td>\n",
       "      <td>School of Business</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name                 city country  \\\n",
       "0     Avery Ao              Taiyuan   China   \n",
       "1  Dongrui Bai                 Xian   China   \n",
       "2     Tony Bai       Beijing 100062   China   \n",
       "3   Yuchen Bai              Beijing   China   \n",
       "4     Xinya Bi  Jinan City Shandong   China   \n",
       "\n",
       "                                     school               list  \n",
       "0  Colege of Agricultural and Life Sciences        Dean's List  \n",
       "1               Colege of Letters & Science        Dean's List  \n",
       "2                     Colege of Engineering  Dean's Honor List  \n",
       "3               Colege of Letters & Science        Dean's List  \n",
       "4                        School of Business        Dean's List  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert neat pdf to neater dataframe\n",
    "raw_df = pd.DataFrame(tabula.read_pdf(\"./raw_data/deanlist_2017_fall_mainland.pdf\", pages=\"all\"))\n",
    "raw_df.columns = [\"name\", \"city\", \"country\", \"school\", \"list\"]\n",
    "raw_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>school</th>\n",
       "      <th>list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Christie Cheng</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Meggie Cook</td>\n",
       "      <td>Clearwater Bay Kowloon</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Christie Leung</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tess Lo</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "      <td>Dean's List</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yi Luo</td>\n",
       "      <td>KOWLOON</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>School of Human Ecology</td>\n",
       "      <td>Dean's Honor List</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             name                    city    country  \\\n",
       "0  Christie Cheng               Hong Kong  Hong Kong   \n",
       "1     Meggie Cook  Clearwater Bay Kowloon  Hong Kong   \n",
       "2  Christie Leung               Hong Kong  Hong Kong   \n",
       "3         Tess Lo               Hong Kong  Hong Kong   \n",
       "4          Yi Luo                 KOWLOON  Hong Kong   \n",
       "\n",
       "                                     school               list  \n",
       "0  Colege of Agricultural and Life Sciences        Dean's List  \n",
       "1               Colege of Letters & Science        Dean's List  \n",
       "2  Colege of Agricultural and Life Sciences        Dean's List  \n",
       "3               Colege of Letters & Science        Dean's List  \n",
       "4                   School of Human Ecology  Dean's Honor List  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_df_hk = pd.DataFrame(tabula.read_pdf(\"./raw_data/deanlist_2017_fall_hongkong.pdf\", pages=\"all\"))\n",
    "raw_df_hk.columns = [\"name\", \"city\", \"country\", \"school\", \"list\"]\n",
    "raw_df_hk.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>list</th>\n",
       "      <th>school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wei-Ting Chen</td>\n",
       "      <td>Taoyuan City</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tina Cheng</td>\n",
       "      <td>Taipei City</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>High Honor Rol</td>\n",
       "      <td>School of Pharmacy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ting-Chia Kan</td>\n",
       "      <td>Taoyuan</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Justine Kao</td>\n",
       "      <td>Kaohsiung</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Amy Lin</td>\n",
       "      <td>Taichung</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name          city country            list  \\\n",
       "0  Wei-Ting Chen  Taoyuan City  Taiwan     Dean's List   \n",
       "1     Tina Cheng   Taipei City  Taiwan  High Honor Rol   \n",
       "2  Ting-Chia Kan       Taoyuan  Taiwan     Dean's List   \n",
       "3    Justine Kao     Kaohsiung  Taiwan     Dean's List   \n",
       "4        Amy Lin      Taichung  Taiwan     Dean's List   \n",
       "\n",
       "                                     school  \n",
       "0               Colege of Letters & Science  \n",
       "1                        School of Pharmacy  \n",
       "2               Colege of Letters & Science  \n",
       "3  Colege of Agricultural and Life Sciences  \n",
       "4  Colege of Agricultural and Life Sciences  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Need to specially handle taiwan data, tabula has some issue\n",
    "raw_df_tw = pd.DataFrame(tabula.read_pdf(\"./raw_data/deanlist_2017_fall_taiwan.pdf\", pages=\"all\"))\n",
    "raw_df_tw.columns = [\"name\", \"city\", \"country\", \"list\"]\n",
    "for index, row in raw_df_tw.iterrows():\n",
    "    raw_df_tw.loc[index, \"school\"] = re.search('China\\s(.+)', row[\"country\"]).groups()[0]\n",
    "    raw_df_tw.loc[index, \"country\"] = \"Taiwan\"\n",
    "raw_df_tw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>list</th>\n",
       "      <th>school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wei-Ting Chen</td>\n",
       "      <td>taoyuan</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tina Cheng</td>\n",
       "      <td>taipei</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>High Honor Rol</td>\n",
       "      <td>School of Pharmacy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ting-Chia Kan</td>\n",
       "      <td>taoyuan</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Justine Kao</td>\n",
       "      <td>kaohsiung</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Amy Lin</td>\n",
       "      <td>taichung</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name       city country            list  \\\n",
       "0  Wei-Ting Chen    taoyuan  Taiwan     Dean's List   \n",
       "1     Tina Cheng     taipei  Taiwan  High Honor Rol   \n",
       "2  Ting-Chia Kan    taoyuan  Taiwan     Dean's List   \n",
       "3    Justine Kao  kaohsiung  Taiwan     Dean's List   \n",
       "4        Amy Lin   taichung  Taiwan     Dean's List   \n",
       "\n",
       "                                     school  \n",
       "0               Colege of Letters & Science  \n",
       "1                        School of Pharmacy  \n",
       "2               Colege of Letters & Science  \n",
       "3  Colege of Agricultural and Life Sciences  \n",
       "4  Colege of Agricultural and Life Sciences  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reserve an original copy for reference\n",
    "df = raw_df.copy()\n",
    "df_tw = raw_df_tw.copy()\n",
    "df_hk = raw_df_hk.copy()\n",
    "\n",
    "# Clean the dataframe\n",
    "df[\"city\"] = df[\"city\"].apply(lambda city: re.split('\\W+', city.lower())[0])\n",
    "df_tw[\"city\"] = df_tw[\"city\"].apply(lambda city: re.split('\\W+', city.lower())[0])\n",
    "df_hk[\"city\"] = \"hongkong\"\n",
    "\n",
    "# Further clean taiwan city names\n",
    "df_tw.loc[df_tw[\"city\"] == \"new\", \"city\"] = \"newtaipei\"\n",
    "df_tw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(685, 5)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenate three dataframes\n",
    "frames = [df, df_hk, df_tw]\n",
    "tmp = pd.concat(frames, ignore_index=True)\n",
    "df = tmp[[\"name\", \"city\", \"country\", \"school\", \"list\"]]\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "112\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'anqing',\n",
       " 'anshan',\n",
       " 'baoding',\n",
       " 'beijing',\n",
       " 'beijng',\n",
       " 'binzhou',\n",
       " 'changchun',\n",
       " 'changde',\n",
       " 'changsha',\n",
       " 'changshu',\n",
       " 'changzhou',\n",
       " 'chengdu',\n",
       " 'chongqing',\n",
       " 'dalian',\n",
       " 'daqing',\n",
       " 'dongiyng',\n",
       " 'dunhuang',\n",
       " 'foshan',\n",
       " 'fuding',\n",
       " 'fuzhou',\n",
       " 'gaoyou',\n",
       " 'guangzhou',\n",
       " 'gui',\n",
       " 'guilin',\n",
       " 'guiyang',\n",
       " 'haikou',\n",
       " 'hangzhou',\n",
       " 'harbin',\n",
       " 'hefei',\n",
       " 'hohhot',\n",
       " 'hongkong',\n",
       " 'huizhou',\n",
       " 'jianyang',\n",
       " 'jiaxing',\n",
       " 'jinan',\n",
       " 'jinhua',\n",
       " 'jining',\n",
       " 'jinzhou',\n",
       " 'kaifeng',\n",
       " 'kaohsiung',\n",
       " 'karamay',\n",
       " 'kunming',\n",
       " 'kunshan',\n",
       " 'langfang',\n",
       " 'lanzhou',\n",
       " 'leshan',\n",
       " 'liaoyang',\n",
       " 'linyi',\n",
       " 'loudi',\n",
       " 'luoyang',\n",
       " 'luzhou',\n",
       " 'maanshan',\n",
       " 'meishan',\n",
       " 'mianyang',\n",
       " 'nanchang',\n",
       " 'nanjing',\n",
       " 'nanning',\n",
       " 'nantong',\n",
       " 'nanyang',\n",
       " 'newtaipei',\n",
       " 'ningbo',\n",
       " 'pudong',\n",
       " 'qingdao',\n",
       " 'quanzhou',\n",
       " 'quzhou',\n",
       " 'sh',\n",
       " 'shanghai',\n",
       " 'shantou',\n",
       " 'shaoxing',\n",
       " 'shengzhou',\n",
       " 'shenyang',\n",
       " 'shenzhen',\n",
       " 'shijiazhuang',\n",
       " 'shouguang',\n",
       " 'suqian',\n",
       " 'suzhou',\n",
       " 'taian',\n",
       " 'taichung',\n",
       " 'tainan',\n",
       " 'taipei',\n",
       " 'taiyuan',\n",
       " 'taiyuanshi',\n",
       " 'tangshan',\n",
       " 'taoyuan',\n",
       " 'tianjin',\n",
       " 'tianshui',\n",
       " 'urumqi',\n",
       " 'weifang',\n",
       " 'weihai',\n",
       " 'wenzhou',\n",
       " 'wuhan',\n",
       " 'wuxi',\n",
       " 'xi',\n",
       " 'xiamen',\n",
       " 'xian',\n",
       " 'xiangtan',\n",
       " 'xinyu',\n",
       " 'xuzhou',\n",
       " 'yancheng',\n",
       " 'yangzhou',\n",
       " 'yantai',\n",
       " 'yiwu',\n",
       " 'yongtaizhuang',\n",
       " 'zhangjiagang',\n",
       " 'zhengzhou',\n",
       " 'zhenjiang',\n",
       " 'zhongqing',\n",
       " 'zhongshan',\n",
       " 'zhuhai',\n",
       " 'zhuji',\n",
       " 'zibo',\n",
       " 'zunyi'}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(len(set(df[\"city\"])))\n",
    "set(df[\"city\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "beijng\n",
      "pudong\n",
      "xi\n",
      "dongiyng\n",
      "sh\n",
      "xi\n",
      "xi\n",
      "xi\n",
      "xi\n",
      "zhongqing\n",
      "yongtaizhuang\n",
      "xi\n",
      "gui\n",
      "xi\n",
      "xi\n",
      "taiyuanshi\n",
      "taiyuanshi\n"
     ]
    }
   ],
   "source": [
    "# Some city names are not still not cleaned, such as `sh`\n",
    "# Those case are still minor, so we can fix it manually\n",
    "city_coord = load(open(\"china_coord.json\", \"r\"))\n",
    "for c in df[\"city\"]:\n",
    "    if c not in city_coord:\n",
    "        print(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fix students' typo\n",
    "pd.options.mode.chained_assignment = None \n",
    "df.drop(df.index[df['city'] == \"sh\"], inplace=True)\n",
    "df.loc[df.index[df['city'] == \"beijng\"], 'city'] = \"beijing\"\n",
    "df.loc[df.index[df['city'] == \"xi\"], 'city'] = \"xi'an\"\n",
    "df.loc[df.index[df['city'] == \"xian\"], 'city'] = \"xi'an\"\n",
    "df.loc[df.index[df['city'] == \"pudong\"], 'city'] = \"shanghai\"\n",
    "df.loc[df.index[df['city'] == \"dongiyng\"], 'city'] = \"dongying\"\n",
    "df.loc[df.index[df['city'] == \"zhongqing\"], 'city'] = \"chongqing\"\n",
    "df.loc[df.index[df['city'] == \"yongtaizhuang\"], 'city'] = \"beijing\"\n",
    "df.loc[df.index[df['city'] == \"gui\"], 'city'] = \"guiyang\"\n",
    "df.loc[df.index[df['city'] == \"taiyuanshi\"], 'city'] = \"taiyuan\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>school</th>\n",
       "      <th>list</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avery Ao</td>\n",
       "      <td>taiyuan</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Agricultural and Life Sciences</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>37.8903</td>\n",
       "      <td>112.5509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dongrui Bai</td>\n",
       "      <td>xi'an</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>34.2778</td>\n",
       "      <td>108.9531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tony Bai</td>\n",
       "      <td>beijing</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Engineering</td>\n",
       "      <td>Dean's Honor List</td>\n",
       "      <td>39.9300</td>\n",
       "      <td>116.3956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Yuchen Bai</td>\n",
       "      <td>beijing</td>\n",
       "      <td>China</td>\n",
       "      <td>Colege of Letters &amp; Science</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>39.9300</td>\n",
       "      <td>116.3956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Xinya Bi</td>\n",
       "      <td>jinan</td>\n",
       "      <td>China</td>\n",
       "      <td>School of Business</td>\n",
       "      <td>Dean's List</td>\n",
       "      <td>36.6828</td>\n",
       "      <td>117.0250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name     city country                                    school  \\\n",
       "0     Avery Ao  taiyuan   China  Colege of Agricultural and Life Sciences   \n",
       "1  Dongrui Bai    xi'an   China               Colege of Letters & Science   \n",
       "2     Tony Bai  beijing   China                     Colege of Engineering   \n",
       "3   Yuchen Bai  beijing   China               Colege of Letters & Science   \n",
       "4     Xinya Bi    jinan   China                        School of Business   \n",
       "\n",
       "                list      lat      long  \n",
       "0        Dean's List  37.8903  112.5509  \n",
       "1        Dean's List  34.2778  108.9531  \n",
       "2  Dean's Honor List  39.9300  116.3956  \n",
       "3        Dean's List  39.9300  116.3956  \n",
       "4        Dean's List  36.6828  117.0250  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add latitude and longtitude info the dataframe\n",
    "lat, long = [], []\n",
    "for index, row in df.iterrows():\n",
    "    df.loc[index, \"lat\"] = city_coord[row[\"city\"]][\"coord\"][0]\n",
    "    df.loc[index, \"long\"] = city_coord[row[\"city\"]][\"coord\"][1]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the dataframe to a csv\n",
    "df.to_csv(\"deanlist_2017_fall.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
